package bookpos.dao;

import java.util.ArrayList;
import java.util.List;

import org.springframework.stereotype.Repository;

import bookpos.entity.Orders;
import bookpos.util.core.dao.HibernateDao;
import bookpos.vo.BookTypeLedger;
import bookpos.vo.MonthLedger;
@Repository
public class LedgerDao extends HibernateDao<Orders> {

	public List<MonthLedger> findMonthLedger() {
		
		String sql = "select count(*) as number,sum(total) as total from bookpos_orders";
		List<Object[]> q = getCurrentSession().createSQLQuery(sql).list();
		List<MonthLedger> _list = new ArrayList<MonthLedger>();
		for(int i = 0; i < q.size(); i++){
			MonthLedger m = new MonthLedger();
			m.setMonth("7月份");
			m.setNumber(q.get(i)[0]+"");
			m.setTotal(q.get(i)[1]+"");
			_list.add(m);
		}
		return _list;
	}
	
	public List<BookTypeLedger> findBookTypeLedger() {
		
		String sql = "select btid,type_name,sum(i.quantity),sum(total) from bookpos_order_item i,bookpos_book b,bookpos_book_type t where i.bid=b.id and t.id=b.btid group by b.btid";
		List<Object[]> q = getCurrentSession().createSQLQuery(sql).list();
		List<BookTypeLedger> _list = new ArrayList<BookTypeLedger>();
		for(int i = 0; i < q.size(); i++){
			BookTypeLedger m = new BookTypeLedger();
			m.setBtid(q.get(i)[0]+"");
			m.setTypeName(q.get(i)[1]+"");
			m.setQuantitys(q.get(i)[2]+"");
			m.setTotals(q.get(i)[3]+"");
			_list.add(m);
		}
		return _list;
	}
	
	//select btid,bookname,type_name,i.quantity,i.total,sum(i.quantity),sum(total) from bookpos_order_item i,bookpos_book b,bookpos_book_type t where i.bid=b.id and t.id=b.btid group by b.btid;
}
